package com.ljqc.sbom.management.repository;


import com.alibaba.fastjson.JSONObject;
import com.ljqc.sbom.management.domain.SpdxPackage;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface SpdxPackageRepository extends JpaRepository<SpdxPackage, Integer> {

    @Query(nativeQuery = true, value = "SELECT count(*) from spdx_package where spdx_project_id = ?1")
    Integer findPackageCount(Integer spdxProjectId);

    @Query(nativeQuery = true, value = "SELECT  " +
            "  *   " +
            "FROM  " +
            "  (  " +
            "  SELECT  " +
            "  *   " +
            "  FROM  " +
            "    (  " +
            "    SELECT  " +
            "      id,  " +
            "      repo_name,  " +
            "      version,  " +
            "      platform,  " +
            "      inhouse,  " +
            "      license,  " +
            "      license_relation,  " +
            "      lj_package_id,  " +
            "      package_file_name,  " +
            "      home_page,  " +
            "      summary,  " +
            "      description,  " +
            "      download_location,  " +
            "      spdx_id,  " +
            "      supplier,  " +
            "      originator,  " +
            "      source_info,  " +
            "      license_concluded,  " +
            "      license_declared,  " +
            "      license_comments,  " +
            "      copyright_text,  " +
            "      verification_code,  " +
            "      comment,  " +
            "      checksum,  " +
            "      relationship   " +
            "    FROM  " +
            "      spdx_package   " +
            "    WHERE  " +
            "      spdx_project_id = ?1   " +
            "   and if(?2 is null,true,lower(repo_name) like lower(CONCAT( '%',?2, '%' ))) " +
            "   and if(?4 is null,true,inhouse = ?4) " +
            "   and if(?3 is null,true,platform = ?3) " +
            "    ) repo  " +
            "    LEFT JOIN (  " +
            "    SELECT  " +
            "      `d`.`spdx_package_id` AS `spdx_package_id`,  " +
            "      max( ( CASE `d`.`vul_level` WHEN 4 THEN `d`.`count` ELSE 0 END ) ) AS `critical`,  " +
            "      max( ( CASE `d`.`vul_level` WHEN 3 THEN `d`.`count` ELSE 0 END ) ) AS `high`,  " +
            "      max( ( CASE `d`.`vul_level` WHEN 2 THEN `d`.`count` ELSE 0 END ) ) AS `mid`,  " +
            "      max( ( CASE `d`.`vul_level` WHEN 1 THEN `d`.`count` ELSE 0 END ) ) AS `low`,  " +
            "      max( ( CASE `d`.`vul_level` WHEN 0 THEN `d`.`count` ELSE 0 END ) ) AS `unknow`   " +
            "    FROM  " +
            "      (  " +
            "      SELECT  " +
            "        `v`.`spdx_package_id` AS `spdx_package_id`,  " +
            "        `v`.`vul_level` AS `vul_level`,  " +
            "        count( 0 ) AS `count`   " +
            "      FROM  " +
            "        `spdx_vul` v   " +
            "      WHERE  " +
            "        ( ( `v`.`vul_id` IS NOT NULL ) AND ( `v`.`spdx_package_id` IS NOT NULL ) AND `v`.`spdx_project_id` = ?1 )   " +
            "      GROUP BY  " +
            "        `v`.`spdx_package_id`,  " +
            "        `v`.`vul_level`   " +
            "      ) `d`   " +
            "    GROUP BY  " +
            "      `d`.`spdx_package_id`   " +
            "    ) d ON repo.id = d.spdx_package_id   " +
            "  ) vi   ",
            countQuery = "SELECT  " +
                    "  *   " +
                    "FROM  " +
                    "  (  " +
                    "  SELECT  " +
                    "  *   " +
                    "  FROM  " +
                    "    (  " +
                    "    SELECT  " +
                    "      id,  " +
                    "      repo_name,  " +
                    "      version,  " +
                    "      platform,  " +
                    "      inhouse,  " +
                    "      license,  " +
                    "      license_relation,  " +
                    "      package_file_name,  " +
                    "      lj_package_id,  " +
                    "      home_page,  " +
                    "      summary,  " +
                    "      description,  " +
                    "      download_location,  " +
                    "      spdx_id,  " +
                    "      supplier,  " +
                    "      originator,  " +
                    "      source_info,  " +
                    "      license_concluded,  " +
                    "      license_declared,  " +
                    "      copyright_text,  " +
                    "      verification_code,  " +
                    "      comment,  " +
                    "      license_comments,  " +
                    "      checksum,  " +
                    "      relationship   " +
                    "    FROM  " +
                    "      spdx_package   " +
                    "    WHERE  " +
                    "      spdx_project_id = ?1   " +
                    "   and if(?2 is null,true,lower(repo_name) like lower(CONCAT( '%',?2, '%' ))) " +
                    "   and if(?3 is null,true,platform = ?3) " +
                    "   and if(?4 is null,true,inhouse = ?4) " +
                    "    ) repo  " +
                    "    LEFT JOIN (  " +
                    "    SELECT  " +
                    "      `d`.`spdx_package_id` AS `spdx_package_id`,  " +
                    "      max( ( CASE `d`.`vul_level` WHEN 4 THEN `d`.`count` ELSE 0 END ) ) AS `critical`,  " +
                    "      max( ( CASE `d`.`vul_level` WHEN 3 THEN `d`.`count` ELSE 0 END ) ) AS `high`,  " +
                    "      max( ( CASE `d`.`vul_level` WHEN 2 THEN `d`.`count` ELSE 0 END ) ) AS `mid`,  " +
                    "      max( ( CASE `d`.`vul_level` WHEN 1 THEN `d`.`count` ELSE 0 END ) ) AS `low`,  " +
                    "      max( ( CASE `d`.`vul_level` WHEN 0 THEN `d`.`count` ELSE 0 END ) ) AS `unknow`   " +
                    "    FROM  " +
                    "      (  " +
                    "      SELECT  " +
                    "        `v`.`spdx_package_id` AS `spdx_package_id`,  " +
                    "        `v`.`vul_level` AS `vul_level`,  " +
                    "        count( 0 ) AS `count`   " +
                    "      FROM  " +
                    "        `spdx_vul` v   " +
                    "      WHERE  " +
                    "        ( ( `v`.`vul_id` IS NOT NULL ) AND ( `v`.`spdx_package_id` IS NOT NULL ) AND `v`.`spdx_project_id` = ?1 )   " +
                    "      GROUP BY  " +
                    "        `v`.`spdx_package_id`,  " +
                    "        `v`.`vul_level`   " +
                    "      ) `d`   " +
                    "    GROUP BY  " +
                    "      `d`.`spdx_package_id`   " +
                    "    ) d ON repo.id = d.spdx_package_id   " +
                    "  ) vi   ")
    Page<JSONObject> getPackageList(Integer spdxProjectId, String repoName, String platform, Integer inhouse, Pageable page);

    @Query(nativeQuery = true, value = "select DISTINCT platform from spdx_package WHERE spdx_project_id = ?1 and platform is not null")
    List<String> getPlatformList(Integer spdxProjectId);


    @Query(nativeQuery = true, value = "SELECT" +
            " count( id ) as count," +
            " vul_level  as level" +
            "  FROM" +
            " (" +
            " SELECT " +
            " r.id, " +
            " r.repo_name, " +
            " r.version, " +
            " r.platform, " +
            " MAX( t1.vul_level ) AS vul_level  " +
            " FROM " +
            " spdx_package r " +
            " LEFT JOIN spdx_vul t1 ON r.id = t1.spdx_package_id   " +
            " WHERE" +
            " t1.spdx_project_id = ?1 " +
            " GROUP BY" +
            " r.repo_name, " +
            " r.version, " +
            " r.platform, " +
            " r.id " +
            ") a  " +
            " GROUP BY " +
            " vul_level")
    List<JSONObject> getCountByVulLevel(Integer projectId);

    @Query(nativeQuery = true, value = "SELECT " +
            " count( 1 )  " +
            "FROM " +
            " spdx_package  " +
            "WHERE " +
            " spdx_project_id = ?1  " +
            " AND id NOT IN ( " +
            " SELECT " +
            "  p.id  " +
            " FROM " +
            "  spdx_vul v " +
            "  LEFT JOIN spdx_package p ON p.id = v.spdx_package_id  " +
            " WHERE " +
            "  v.spdx_project_id = ?1  " +
            " ) ")
    int getCountNoVulLevel(Integer projectId);

    @Query(nativeQuery = true, value = "SELECT " +
            " count( 1 ) AS count, " +
            " risk_level AS level  " +
            "FROM " +
            " ( " +
            " SELECT " +
            "  r.id, " +
            "  r.repo_name, " +
            "  r.version, " +
            "  r.platform, " +
            "  b.risk_level, " +
            "  b.license  " +
            " FROM " +
            "  spdx_package r " +
            "  LEFT JOIN ( " +
            "  SELECT " +
            "   t.*, " +
            "   row_number ( ) over ( PARTITION BY t.spdx_package_id ORDER BY risk_level DESC ) AS rs_num  " +
            "  FROM " +
            "   spdx_license t  " +
            "  WHERE " +
            "   spdx_project_id = ?1  " +
            "  ) b ON r.id = b.spdx_package_id  " +
            " WHERE " +
            "  r.spdx_project_id = ?1  " +
            " GROUP BY " +
            "  r.repo_name, " +
            "  r.version, " +
            "  r.platform,  " +
            "  r.id  " +
            " ) a  " +
            "GROUP BY " +
            " risk_level  " +
            "ORDER BY " +
            " risk_level")
    List<JSONObject> getCountByLicenseLevel(Integer projectId);

    @Query(nativeQuery = true, value = "SELECT" +
            " count( 1 ) " +
            "FROM" +
            " spdx_package " +
            "WHERE" +
            " spdx_project_id = ?1 " +
            " AND id NOT IN ( SELECT p.id FROM spdx_license l LEFT JOIN spdx_package p ON p.id = l.spdx_package_id WHERE l.spdx_project_id = ?1 )")
    int getCountByNoLicenseLevel(Integer projectId);

    @Query(nativeQuery = true, value = "SELECT " +
            " repo_name name, " +
            " version, " +
            " lj_package_id, " +
            " platform, " +
            " package_file_name path " +
            "FROM " +
            " spdx_package " +
            "WHERE " +
            " id IN ( SELECT spdx_package_id FROM spdx_license WHERE spdx_project_id = ?1 AND license = ?2 )",
    countQuery = "SELECT count(*)" +
            "FROM " +
            " spdx_package  " +
            "WHERE " +
            " id IN ( SELECT spdx_package_id FROM spdx_license WHERE spdx_project_id = ?1 AND license = ?2 )")
    Page<JSONObject> getPackageByLicense(Integer spdxProjectId, String name, Pageable page);


    @Query(nativeQuery = true,value = "SELECT ri.repo_name name,ri.version version,ri.package_file_name path, ri.lj_package_id, ri.platform " +
            "FROM spdx_vul d left JOIN spdx_package ri on d.spdx_package_id=ri.id " +
            "WHERE d.spdx_project_id =?1 and d.vul_id=?2 ",
            countQuery = "SELECT count(*)  " +
                    "FROM spdx_vul d left JOIN spdx_package ri on d.spdx_package_id=ri.id " +
                    "WHERE d.spdx_project_id =?1 and d.vul_id=?2 ")
    Page<JSONObject> getPackageByVul(Integer spdxProjectId, String name, Pageable page);

    @Query(nativeQuery = true, value = "select DISTINCT package_file_name as filePath from spdx_package where " +
            " package_file_name is not null " +
            " and spdx_project_id = ?4 " +
            " and if(?1 is null,true,repo_name = ?1) " +
            " and if(?2 is null,true,platform = ?2)" +
            " and if(?3 is null,true,version = ?3)")
    List<String> getPackageFilePath(String name, String platform, String version, Integer spdxProjectId);

    SpdxPackage getSpdxPackageById(Integer id);

    @Transactional
    void deleteAllBySpdxProjectId(Integer projectId);

    @Query(nativeQuery = true, value = "SELECT * from spdx_package where spdx_project_id = ?1")
    List<SpdxPackage> findPackageByProjectId(Integer spdxProjectId);
}
